Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Indexes

An index is an optional structure designed to help you achieve faster access to your data. Just like the index in this book, an Oracle index is logically and physically independent of the data in the associated table or cluster. You can use the index to speed access to the data, or you can retrieve the data independently from the index by searching the tables for it. When optimally configured and used, indexes can significantly reduce I/O to the data files and greatly improve performance.

The presence of an index is transparent to the user or application and requires no application changes. However, if you know of the existence of the index and design the application to take advantage of the index, you can greatly reduce the I/Os necessary to retrieve the desired data. The only indication of an index may be improved time to access the data.

Once an index has been created on a table, the maintenance of that index is done automatically by Oracle. Inserts, updates, and deletions of rows are automatically updated in the related indexes.

A table can have any number of indexes, but the more indexes there are, the more overhead is incurred during table updates, inserts, and deletions. This overhead is incurred because all associated indexes must be updated whenever table data is altered.


TIP:  If you use the Oracle Parallel Query option, you can create the index in parallel, thus reducing the time it takes to create the index.

Index Types

There are several different types of indexes:


Index Type Description

Unique index A unique index is an index value that has the additional constraint that it cannot be duplicated. Although this constraint may be specified, it is usually better to associate this constraint with the table itself rather than with the index. Oracle enforces unique integrity constraints by automatically defining a unique index on the unique key.
Nonunique index A nonunique index does not impose the constraint that the index value be unique. Such an index is useful if you need quick access to a nonunique value.
Cluster index A cluster index is an index created on the cluster key in an Oracle cluster. It is required that a cluster key be indexed.
Composite index A composite index is an index on several columns in a table. The column values can be in any order and the columns do not have to be adjacent in the table.

A composite index is useful when SELECT statements have WHERE clauses that reference several values in the table. Because the index is accessed based on the order of the columns used in the definition, it is wise to base the index order on the frequency of use. The most referenced column should be defined first, and so on.

The index should be created based on the values accessed in the application; the application should be developed to take advantage of the indexes. Having knowledge of and influence over these indexes can be very useful to the application developer.

What To Index

The index is usually determined by the column values that are indexed. Remember that the more indexes on a table, the more overhead is incurred during updates, inserts, and deletes. It is important to index selectively.

Which Tables Should Be Indexed?

Use the following guidelines to decide which tables to index:

  Index tables when queries select only a small number of rows. Queries that select a large number of rows defeat the purpose of the index. Index the table when queries access less than 5 percent of the rows in the table.
  Don’t index tables that are frequently updated. Update, insert, and deletes on indexed tables incur extra overhead. Base your decision about whether or not to index a table on the number of updates, inserts, and deletes relative to the total number of queries to the table.
  Index tables that don’t have duplicate values on the columns usually selected in WHERE clauses. Tables for which the selection is based on TRUE/FALSE values are not good candidates for an index.
  Index tables that are queried with relatively simple WHERE clauses. Complex WHERE clauses may not be able to take advantage of indexes. You can solve this shortcoming by creating a complex index, by simplifying the SQL statement, or by using a hint.

Once you decide to use an index, you must then decide which columns to put the index on. You may index one or more columns, depending on the table.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.